home *** CD-ROM | disk | FTP | other *** search
/ Programmer Plus 2007 / Programmer-Plus-2007.iso / Programming / SQL and Data Base / Logic Works ERwin 3.5 / data.1 / erwuiein.fiv < prev    next >
Encoding:
Text File  |  1998-01-28  |  6.1 KB  |  178 lines
  1. /* ------------------------------------------------------------------------
  2. --    ERWUIEIN.FIV                                                          --
  3. --    Copyright (C) Logic Works, Inc. 1994                                 --
  4. --                                                                       --
  5. --  Stored function to insert ERwin primary keys or                         --
  6. --  identifying relationships into the Oracle CASE 5.1 dictionary as     --
  7. --  UIEs                                                                 --
  8. ------------------------------------------------------------------------- */
  9. CREATE OR REPLACE FUNCTION ERWUIEIN(pAttRel CI_UNIQUE_IDENTIFIER_ENTRIES.ATTRIBUTE_OR_RELATION%TYPE,   
  10.                      pEntName CI_ENTITIES.NAME%TYPE,
  11.                      pEntID      CI_ENTITIES.ID%TYPE,
  12.                      pOtherEntID CI_ENTITIES.ID%TYPE,
  13.                      pAttRelName CI_UNIQUE_IDENTIFIER_ENTRIES.RELATIONSHIP_NAME%TYPE,
  14.                      pApp        CI_APPLICATION_SYSTEMS.NAME%TYPE,
  15.                      pVersion    CI_APPLICATION_SYSTEMS.VERSION%TYPE)
  16.                 RETURN CI_UNIQUE_IDENTIFIER_ENTRIES.ID%TYPE                             
  17.     AS
  18.     TYPE recEntity IS RECORD
  19.         (APPLICATION_SYSTEM_OWNED_BY   CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY%TYPE,
  20.          ID CI_ENTITIES.ID%TYPE,
  21.          NAME CI_ENTITIES.NAME%TYPE);
  22.     TYPE recAttribute IS RECORD
  23.         (ENTITY_REFERENCE CI_ATTRIBUTES.ENTITY_REFERENCE%TYPE,
  24.          ID                  CI_ATTRIBUTES.ID%TYPE,
  25.          NAME              CI_ATTRIBUTES.NAME%TYPE);
  26.      TYPE recRelend IS RECORD
  27.         (DEGREE    CI_RELATIONSHIP_ENDS.DEGREE%TYPE,
  28.          FROM_ENTITY_REFERENCE    CI_RELATIONSHIP_ENDS.FROM_ENTITY_REFERENCE%TYPE,
  29.          ID CI_RELATIONSHIP_ENDS.ID%TYPE,
  30.          NAME CI_RELATIONSHIP_ENDS.NAME%TYPE,
  31.          TO_ENTITY_REFERENCE   CI_RELATIONSHIP_ENDS.TO_ENTITY_REFERENCE%TYPE);
  32.     TYPE recUniqueIE is RECORD
  33.         (ATTRIBUTE_OR_RELATION    CI_UNIQUE_IDENTIFIER_ENTRIES.ATTRIBUTE_NAME%TYPE,
  34.          ATTRIBUTE_REFERENCE    CI_ATTRIBUTES.ID%TYPE,
  35.          ENTITY_REFERENCE        CI_ENTITIES.ID%TYPE,
  36.          ID                        CI_UNIQUE_IDENTIFIER_ENTRIES.ID%TYPE,
  37.          RELATIONSHIP_END_REFERENCE CI_UNIQUE_IDENTIFIER_ENTRIES.RELATIONSHIP_END_REFERENCE%TYPE);
  38.     prUIE            recUniqueIE;
  39.     stUIE            ciounique_identifier_entry.data;
  40.     prEntity        recEntity;
  41.     prAttribute        recAttribute;
  42.     prRelend        recRelend;
  43.     EntityRef        CI_ENTITIES.ID%TYPE;
  44.     AttRelRef        CI_ATTRIBUTES.ID%TYPE;
  45.     nSequenceNumber CI_UNIQUE_IDENTIFIER_ENTRIES.SEQUENCE_NUMBER%TYPE;
  46.       act_status        varchar2(100);
  47.     act_warnings    varchar2(100);
  48. BEGIN
  49.     if cdapi.initialized = false then
  50.         RETURN -1;
  51.     end if;
  52.         BEGIN
  53.             IF pVersion = 0 THEN 
  54.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  55.                     FROM CI_ENTITIES
  56.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntName)
  57.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  58.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  59.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  60.                     CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  61.             ELSE
  62.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  63.                     FROM CI_ENTITIES
  64.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntName)
  65.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  66.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  67.                     CI_APPLICATION_SYSTEMS.NAME =UPPER(pApp) AND
  68.                     CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  69.             END IF; 
  70.             EntityRef := prEntity.ID;
  71.         EXCEPTION
  72.             WHEN OTHERS THEN
  73.                 RETURN -1;
  74.         END;
  75.         IF UPPER(pAttRel) != 'A' THEN
  76.             BEGIN
  77.                 SELECT DEGREE,FROM_ENTITY_REFERENCE,ID,
  78.                     NAME, TO_ENTITY_REFERENCE
  79.                     INTO prRelend
  80.                     FROM CI_RELATIONSHIP_ENDS
  81.                     WHERE FROM_ENTITY_REFERENCE=EntityRef
  82.                     AND NAME=pAttRelName;
  83.                 AttRelRef := prRelend.ID;
  84.                 BEGIN
  85.                     SELECT ATTRIBUTE_OR_RELATION,ATTRIBUTE_REFERENCE,
  86.                             ENTITY_REFERENCE,ID,RELATIONSHIP_END_REFERENCE
  87.                             INTO prUIE FROM CI_UNIQUE_IDENTIFIER_ENTRIES
  88.                             WHERE ENTITY_REFERENCE = EntityRef AND
  89.                             RELATIONSHIP_END_REFERENCE = AttRelRef;                  
  90.                     RETURN prUIE.ID;
  91.                 EXCEPTION
  92.                     WHEN NO_DATA_FOUND THEN
  93.                          stUIE.v.RELATIONSHIP_NAME := pAttRelName;
  94.                         stUIE.i.RELATIONSHIP_NAME := true;
  95.                         stUIE.v.RELATIONSHIP_END_REFERENCE := AttRelRef;
  96.                         stUIE.i.RELATIONSHIP_END_REFERENCE := true;
  97.                         stUIE.v.ENTITY_REFERENCE := EntityRef;
  98.                         stUIE.i.ENTITY_REFERENCE := true;
  99.                         stUIE.v.ATTRIBUTE_OR_RELATION := 'R';
  100.                         stUIE.i.ATTRIBUTE_OR_RELATION := true;
  101.                     WHEN OTHERS THEN
  102.                         RETURN -1;
  103.                 END;
  104.             EXCEPTION
  105.                 WHEN OTHERS THEN
  106.                     RETURN -1;
  107.             END;
  108.         ELSE
  109.             BEGIN
  110.                 SELECT ENTITY_REFERENCE, ID, NAME INTO prAttribute
  111.                     FROM CI_ATTRIBUTES
  112.                     WHERE    CI_ATTRIBUTES.NAME=UPPER(pAttRelName)
  113.                     AND CI_ATTRIBUTES.ENTITY_REFERENCE = EntityRef;
  114.                 AttRelRef := prAttribute.ID;
  115.                 BEGIN
  116.                     SELECT ATTRIBUTE_OR_RELATION,ATTRIBUTE_REFERENCE,
  117.                             ENTITY_REFERENCE,ID,RELATIONSHIP_END_REFERENCE
  118.                             INTO prUIE FROM CI_UNIQUE_IDENTIFIER_ENTRIES
  119.                             WHERE ENTITY_REFERENCE = EntityRef AND
  120.                             ATTRIBUTE_REFERENCE = AttRelRef;                  
  121.                     RETURN prUIE.ID;
  122.                 EXCEPTION
  123.                     WHEN NO_DATA_FOUND THEN
  124.                         stUIE.v.ATTRIBUTE_NAME := pAttRelName;
  125.                         stUIE.i.ATTRIBUTE_NAME := true;
  126.                         stUIE.v.ATTRIBUTE_REFERENCE := AttRelRef;
  127.                         stUIE.i.ATTRIBUTE_REFERENCE := true;
  128.                         stUIE.v.ENTITY_REFERENCE := EntityRef;
  129.                         stUIE.i.ENTITY_REFERENCE := true;
  130.                         stUIE.v.ATTRIBUTE_OR_RELATION  := 'A';
  131.                         stUIE.i.ATTRIBUTE_OR_RELATION  := true;
  132.                     WHEN OTHERS THEN
  133.                         RETURN -1;
  134.                 END;
  135.             EXCEPTION
  136.                 WHEN OTHERS THEN
  137.                     RETURN -1;
  138.             END;
  139.         END IF;
  140.         BEGIN
  141.             SELECT MAX(SEQUENCE_NUMBER) INTO nSequenceNumber
  142.                 FROM CI_UNIQUE_IDENTIFIER_ENTRIES
  143.                 WHERE ENTITY_REFERENCE = EntityRef;
  144.                 IF nSequenceNumber is null then
  145.                     nSequenceNumber := 1;
  146.                 ELSE
  147.                     nSequenceNumber := nSequenceNumber + 1;
  148.                 END IF;
  149.         END;
  150.         BEGIN
  151.             stUIE.v.SEQUENCE_NUMBER  := nSequenceNumber;
  152.             stUIE.i.SEQUENCE_NUMBER  := true;
  153.             cdapi.open_activity;
  154.             ciounique_identifier_entry.ins(NULL,stUIE);
  155.             cdapi.validate_activity(act_status, act_warnings);
  156.             cdapi.close_activity(act_status);
  157.             if act_status != 'Y' then
  158.                 cdapi.abort_activity;
  159.                 RETURN -1;
  160.             else
  161.                 RETURN stUIE.v.ID;
  162.             end if;
  163.         EXCEPTION
  164.             WHEN OTHERS THEN
  165.                     if cdapi.stacksize > 0 then 
  166.                         if cdapi.activity is not null then 
  167.                             cdapi.abort_activity;
  168.                         end if;
  169.                     else
  170.                         if cdapi.activity is not null then 
  171.                             cdapi.abort_activity;
  172.                         end if;
  173.                     end if;
  174.                 RETURN -1;
  175.         END;                    
  176. END ERWUIEIN;  
  177. /
  178.